﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using JohnnySins.Entities;
using Dapper;

namespace JohnnySins.DAL.Repositories
{
    public class UserRepository : BaseRepository<User>
    {
        protected override string TableName
        {
            get { return "[User]"; }
        }

        public override IEnumerable<User> FindAll()
        {
            string strSQL = @"  SELECT u.Id, u.UserId, u.UserName, u.FirstName, u.LastName, u.Email, 
                        u.PaymentTypeId, u.CountryId, u.Zip, u.IsAdmin, u.IsActive
                    FROM [User] (nolock) u ";
            return Connection.Query<User>(strSQL);
        }

        public IEnumerable<User> Search(string name, string email, int? paymentTypeId, int? countryId, int? isActive)
        {
            return Connection.Query<User>("spUser_Search @Name, @Email, @PaymentTypeId, @CountryId, @IsActive",
                new { Name = name, Email = email, 
                        PaymentTypeId = (paymentTypeId == -1 ? null : paymentTypeId),
                        CountryId = (countryId == -1 ? null : countryId),
                        IsActive = (isActive == -1 ? null : isActive)});
        }

        public override User Find(int id)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.UserName, u.FirstName, u.LastName, u.Email, 
                        u.PaymentTypeId, u.CountryId, u.Zip, u.IsAdmin, u.IsActive, am.Password
                    FROM [User] (nolock) u left join aspnet_Membership (nolock) as am on u.UserId = am.UserId
                    WHERE u.Id = @userId", new { userId = id }).SingleOrDefault();
        }

        public User Find(Guid userId)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.UserName, u.FirstName, u.LastName, u.Email, 
                        u.PaymentTypeId, u.CountryId, u.Zip, u.IsAdmin, u.IsActive
                    FROM [User] (nolock) u
                    WHERE u.UserId = @userId", new { userId = userId }).SingleOrDefault();
        }

        public User Find(string userName)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.UserName, u.FirstName, u.LastName, u.Email, 
                        u.PaymentTypeId, u.CountryId, u.Zip, u.IsAdmin, u.IsActive
                    FROM [User] (nolock) u
                    WHERE u.UserName = @userName", new { userName = userName }).SingleOrDefault();
        }

        public void AddManual(User newUser, string password)
        {
            Connection.Execute("spUser_Add @UserName, @FirstName, @LastName, @Email, @PaymentTypeId, @CountryId, @Zip, @Password, @PasswordSalt",
                new
                {
                    UserName = newUser.UserName,
                    FirstName = newUser.FirstName,
                    LastName = newUser.LastName,
                    Email = newUser.Email,
                    PaymentTypeId = newUser.PaymentTypeId,
                    CountryId = (newUser.CountryId >= 0 ? newUser.CountryId : null),
                    Zip = newUser.Zip,
                    Password = password,
                    PasswordSalt = GeneratePasswordSalt()
                });
        }

        private string GeneratePasswordSalt()
        {
            RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
            byte[] buff = new byte[32];
            rng.GetBytes(buff);
            return Convert.ToBase64String(buff);
        }

        /*public new string Update(User updateObject)
        {
            return Connection.Query<string>("spUser_Upd @UserId, @UserName, @FirstName, @LastName, @Email, @IsAdmin, @Deleted",
                new
                {
                    UserId = updateObject.Id,
                    UserName = updateObject.UserName,
                    FirstName = updateObject.FirstName,
                    LastName = updateObject.LastName,
                    Email = updateObject.Email,
                    IsAdmin = updateObject.IsAdmin,
                    Deleted = updateObject.Deleted
                }).Single();
        }*/

        public void Activate(int id)
        {
            Connection.Execute(@"UPDATE [User] SET IsActive = 1 WHERE Id = @Id;", new { Id = id });
        }

        public void Deactivate(int id)
        {
            Connection.Execute(@"UPDATE [User] SET IsActive = 0 WHERE Id = @Id;", new { Id = id });
        }

        public IEnumerable<Country> CountryCombo(bool includeEmpty, bool includeAll)
        {
            string strSQL = "";
            if (includeEmpty)
                strSQL = "select 0 as SortOrder, -1 as CountryId, '(Select Country)' as CountryName union all ";
            if (includeAll)
                strSQL = "select 0 as SortOrder, -1 as CountryId, 'All' as CountryName union all ";
            strSQL += "select 1 as SortOrder, CountryId, CountryName from Country order by SortOrder, CountryName";
            return Connection.Query<Country>(strSQL);
        }

        public IEnumerable<PaymentType> PaymentTypeCombo(bool includeEmpty, bool includeAll)
        {
            string strSQL = "";
            if (includeEmpty)
                strSQL = "select 0 as SortOrder, -1 as PaymentTypeId, '(Select Payment Type)' as PaymentTypeName union all ";
            if (includeAll)
                strSQL = "select 0 as SortOrder, -1 as PaymentTypeId, 'All' as PaymentTypeName union all ";
            strSQL += "select 1 as SortOrder, PaymentTypeId, PaymentTypeName from PaymentType order by SortOrder, PaymentTypeName";
            return Connection.Query<PaymentType>(strSQL);
        }
    }
}
